Downloading and Executing Solution File (Dim_Mortgage) 10
· NOTE
We already done one Dimension
Table (Dim_Contract).
We provided Solution File for the
remaining three Dimension tables.
· Download the Solution Files that are Provided for the remaining 3-Dimensional Tables.
· · In the code file there are 3 separate files
· Download one package at
a time (One by One)
· Right click on SSIS Packages ----> Select add
Existing Package
· A Dialogue Box with an option Add Copy of Existing Package appears ----> click on to specify the Path for the Solution File
· · Open File one at a time (Dim_Mortgage) form the Solution File provided
· · Make sure Path for the Solution File is provided ------> Click OK
Note: Repeat the Steps for Dim_Owner and Dim_Property
· · All the packages are loaded.
· Double Click on Dim_Mortage.dtsx
· · Double click on the Data Flow Task to provide Source Connection
· · When Double clicked on the Data Flow Task Package appears as below
· Again Double click on the OLE DB Source - Mortgage Staging View to provide Source Connection
· Click on New to provide New Source Connection Manager
· Make sure New Connection Manager (ColaberryDatabase) is Provided
· Then Click OK
· Once created Connection Manager (ColaberryDatabase) is provided
· · Make sure that the Select Statement is available in the SQL Command Text
· Click OK
Note: Repeat the Steps
for Dim_Owner and Dim_Property
· Double click on First Lookup Dim_Mortgage to provide the Connection for Lookup
· Lookup transformation Editor page will pop up like below.
· Then click OK
· Make sure the Select Statement is available in the Use Results of an SQL Query. Click Ok
Note: Repeat the steps for Dim_Owner and Dim_Property, Make sure
1) Dim_Owner, In results of an SQL Query ---- > Select OwnerID from
Dim_Owner
2) Dim_Property, In results of an SQL Query ---- > Select PropertyID
from Dim_Property
· Double click on OLE DB Destination - Inserting new records
· Provide the right Connection Manager (Mortgage Datamart)
· Click OK
· Provide the Target Table (Dim_Mortgage)
· Click OK
Note :Repeat the Steps for 1) Dim_Owner : In the Drop down of Name of the Table or View,
Choose Dim_Owner
2) Dim_Property : In the Drop down of Name of the Table or
View, Choose Dim_Property
· Double click on the Second Lookup, (Lookup - Only Updatable records will pass through)
· Click OK
· Lookup transformation Editor page will pop up like below.
· Provide the Connection Manager (Mortgage Datamart) for the Second Lookup
· Click Connections ---->Click on the Drop Down of OLE DB Connection Manager and Choose (Mortgage Datamart) ----- > Click OK
·
Click on the Drop Down of Name of the Table or View ---- >
Choose Dim_Mortgage
Note :Repeat the Steps for 1) Dim_Owner : In the Drop down of use a Table or View,
Choose Dim_Owner
2) Dim_Property : In the Drop down of use a Table or View, Choose Dim_Property
· · Double Click on the OLE DB Command (OLE DB Command - Updating Existing Records)
· Select Connection Managers
· Click on the Drop-down arrow
· Provide OLE DB Connection Manager (Mortgage Datamart)
· Select the Component Properties
· Make Sure Exec Statement is in SQL Command ---- > Click OK
·
Note: Repeat the Steps
for
1)Dim_Owner (SQL
Command: Exec [dbo].[USP_UpdateDim_Owner]
?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
2)Dim_Property (SQL
Command : Exec [dbo].[USP_UpdateDim_Property]
?,?,?,?,?,?,?,?,?,?,?,?,?)
· Package appears as shown below
· Delete the two Connection Managers that came with the Solution File
· (LAPTOP-GCON8HFS\CB2016SQLSERVER.ColaberryDatabase) and
(LAPTOP-GCON8HFS\CB2016SQLSERVER.Mortgage Datamart)
· Right Click on LAPTOP-GCON8HFS\CB2016SQLSERVER.ColaberryDatabase) and Select Delete
· Right Click on (LAPTOP-GCON8HFS\CB2016SQLSERVER.Mortgage Datamart) and Select Delete
How to download Solution File?